from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'jobdb',
    'charset': 'utf8'
}

# 首页路由
@app.route('/', methods=['GET', 'POST'])
def index():
    # 获取查询参数
    job_name = request.args.get('job_name', '')  # 从 URL 参数中获取职位名称
    category = request.args.get('category', '')  # 从 URL 参数中获取职位类别
    page = int(request.args.get('page', 1))  # 从 URL 参数中获取当前页码，默认为 1
    per_page = 10  # 每页显示的记录数

    # 连接数据库
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    # 查询职位数据
    sql = '''
        SELECT id, job_name, company_name, job_salary, job_address, job_label
        FROM jobs
        WHERE job_name LIKE %s AND job_label LIKE %s
        LIMIT %s OFFSET %s
    '''
    cursor.execute(sql, (f'%{job_name}%', f'%{category}%', per_page, (page - 1) * per_page))
    jobs = cursor.fetchall()

    # 查询总记录数
    count_sql = '''
        SELECT COUNT(*)
        FROM jobs
        WHERE job_name LIKE %s AND job_label LIKE %s
    '''
    cursor.execute(count_sql, (f'%{job_name}%', f'%{category}%'))
    total = cursor.fetchone()[0]

    # 关闭数据库连接
    cursor.close()
    conn.close()

    # 计算总页数
    total_pages = (total + per_page - 1) // per_page

    # 渲染 HTML 页面
    return render_template(
        'index.html',
        jobs=jobs,
        job_name=job_name,
        category=category,
        page=page,
        total_pages=total_pages
    )

# 职位详情页面路由
@app.route('/job/<int:job_id>')
def job_detail(job_id):
    # 连接数据库
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    # 查询职位详情
    sql = '''
        SELECT job_name, company_name, job_salary, job_address, job_label
        FROM jobs
        WHERE id = %s
    '''
    cursor.execute(sql, (job_id,))
    job = cursor.fetchone()

    # 关闭数据库连接
    cursor.close()
    conn.close()

    if job:
        return render_template('job_detail.html', job=job)
    else:
        return render_template('error.html', message='职位不存在')

if __name__ == '__main__':
    app.run(debug=True)